-- EXEC SP_CREATE_UPDATE_OUT_TRANSACTION_LOT

ALTER PROCEDURE SP_CREATE_UPDATE_OUT_TRANSACTION_LOT (
 
 @PART_NUMBER 		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50),
 @SHIP_DATE 		DATETIME,
 @SHIP_QTY 			INT,
 @VENDOR_NAME 		VARCHAR(50),
 @FAB_LOT_NUMBER 	VARCHAR(50),
 @PS_TYPE_NAME  	VARCHAR(50),	
 @PS_TYPE 			VARCHAR(50),
 @TRANSACTION_TYPE VARCHAR(50),
 @NEXT_LOCATION	   VARCHAR(50),
 @NEXT_PROCESS     VARCHAR(50),
 @BATRCH_LOT		VARCHAR(50),
 @WAFER_ID			VARCHAR(100)
 )
 
 AS
 BEGIN
 
 	DECLARE @LOT_ID INT
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
	DECLARE @RELEASE_LOT_NO VARCHAR(50)
	DECLARE @NEXT_VENDOR_NAME VARCHAR(50)
	DECLARE @BATCHLOT_ID INT
	
	IF @NEXT_LOCATION IS NOT NULL
	BEGIN			
		SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE V_NAME = @NEXT_LOCATION
		IF @NEXT_VENDOR_NAME IS NULL
		BEGIN
			SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT VENDOR_ID FROM ALTERNATE_VENDOR_NAMES WHERE ALTERNATE_VENDOR_NAME = @NEXT_LOCATION )
			IF @NEXT_VENDOR_NAME IS NULL
			BEGIN
				SET @NEXT_VENDOR_NAME = @NEXT_LOCATION
			END
		END
	END	

 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
	SELECT @PROCESS_STEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@PART_NUMBER,@PS_TYPE_NAME,@PS_TYPE )
	IF @PROCESS_STEP_ID IS NULL 
	BEGIN
		 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
		UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
		RETURN
	END

	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	
	IF @TRANSACTION_TYPE = 'Assembly_Out'
	BEGIN 		 
		SELECT TOP 1 @RELEASE_LOT_NO = LT.LOT_NUMBER FROM ASSM_RELEASE_LOT_TABLE ARLT, LOT_TABLE LT 
				WHERE LT.LOT_ID = ARLT.LOT_ID AND ARLT.SHIPPED_LOT_NUMBER = @LOT_NUMBER 
	END
	ELSE IF @TRANSACTION_TYPE NOT IN ( 'Die_Out','BG_Out', 'BFG_Out' )
	BEGIN
		SELECT TOP 1 @RELEASE_LOT_NO = LT.LOT_NUMBER FROM ASSM_RELEASE_LOT_TABLE ARLT, LOT_TABLE LT 
				WHERE LT.LOT_ID = ARLT.LOT_ID AND ARLT.SHIPPED_LOT_NUMBER = 
			(SELECT TOP 1 LOT_NUMBER FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @LOT_NUMBER )	
	END

	IF @FAB_LOT_NUMBER IS NULL
	BEGIN
		SET @FAB_LOT_NUMBER = @RELEASE_LOT_NO
	END

	IF @LOT_ID IS NULL
	BEGIN
		IF @TRANSACTION_TYPE IN ( 'BG_Out', 'BFG_Out' )
		BEGIN
			INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT) 
				VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER,@FAB_LOT_NUMBER, @LOT_NUMBER, @SHIP_QTY, @SHIP_DATE, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0 )
			SELECT @LOT_ID = @@IDENTITY
		END
		ELSE
		BEGIN
			INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER,@FAB_LOT_NUMBER, @LOT_NUMBER, @SHIP_QTY, @SHIP_DATE, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0 )
			SELECT @LOT_ID = @@IDENTITY
		END	

		--For Discrepancy To Find Out Missing INTransaction
		IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @PS_TYPE AND Notification_Name = 'MissedOut InTransaction' ) = 0 
		BEGIN
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Status,TimeStamp)
				VALUES ( @part_number, @lot_number, @FAB_LOT_NUMBER, @vendor_name, @PS_TYPE,'MissedOut InTransaction',  @SHIP_QTY, 'Open', CURRENT_TIMESTAMP)
		END
	END

	IF @LOT_ID IS NOT NULL AND @LOT_ID > 0  		
 	BEGIN

		IF @FAB_LOT_NUMBER IS NOT NULL
		BEGIN
				UPDATE LOT_TABLE SET FAB_LOT_NUMBER = @FAB_LOT_NUMBER WHERE LOT_ID = @LOT_ID 
				UPDATE LOT_TABLE_History SET FAB_LOT_NUMBER = @FAB_LOT_NUMBER WHERE LOT_ID = @LOT_ID
		END
		
		DECLARE @SHIPPEDLOTID INT
		DECLARE @SHIPPEDLOTCOUNT INT
		SET @SHIPPEDLOTCOUNT = 0

		IF @TRANSACTION_TYPE IN ( 'BG_Out', 'BFG_Out' )
		BEGIN
			SELECT @SHIPPEDLOTCOUNT = COUNT(SHIPPEDLOT_ID) FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND STATUS = 'Close' AND SHIP_STATUS > 0 AND SHIP_QTY = @SHIP_QTY AND WAFER_ID = @WAFER_ID
			IF ( @SHIPPEDLOTCOUNT = 0 )
			BEGIN
				SELECT @SHIPPEDLOTID = SHIPPEDLOT_ID FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND STATUS = 'TRANSIT' AND SHIP_STATUS = 0 AND SHIP_QTY = @SHIP_QTY AND WAFER_ID = @WAFER_ID
				--IF @SHIPPEDLOTID IS NULL 
	 			IF ( @@ROWCOUNT = 0 )
	 			BEGIN
					IF @TRANSACTION_TYPE = 'BFG_Out'
					BEGIN
						INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NextLocation, SHIP_DROP_OUT, SL_NEXT_PROCESS) 
							VALUES( @LOT_ID, @SHIP_DATE, @SHIP_QTY, CURRENT_TIMESTAMP, 'Transit', 0,@NEXT_VENDOR_NAME, 0,@NEXT_PROCESS )
					END
					ELSE
					BEGIN
						INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NextLocation, SHIP_DROP_OUT, SL_NEXT_PROCESS) 
							VALUES( @LOT_ID, @SHIP_DATE, @SHIP_QTY, CURRENT_TIMESTAMP, 'Close', 1,@NEXT_VENDOR_NAME,  0, @NEXT_PROCESS )
					END
					SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
				END
				ELSE
				BEGIN
					UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
				END 
			END
			ELSE
			BEGIN
				-- DUMMY UPDATE TO AVOID SQLEXCEPTION "STATEMENTS THAT PRODUCE RESULT SETS ARE NOT ALLOWED IN BATCH COMMANDS."
				SELECT @SHIPPEDLOTID = SHIPPEDLOT_ID FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND STATUS = 'Close' AND SHIP_STATUS > 0 AND SHIP_QTY = @SHIP_QTY AND WAFER_ID = @WAFER_ID
				IF @TRANSACTION_TYPE = 'BFG_Out'
				BEGIN
					UPDATE SHIPPED_LOT SET STATUS = 'Transit', SHIP_STATUS = 0 WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID
				END
				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
			END
		END
		ELSE
		BEGIN
			SELECT @SHIPPEDLOTCOUNT = COUNT(SHIPPEDLOT_ID) FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND STATUS = 'Close' AND SHIP_STATUS > 0 AND SHIP_DATE = @SHIP_DATE AND SHIP_QTY = @SHIP_QTY
			IF ( @SHIPPEDLOTCOUNT = 0 )
			BEGIN
				SELECT @SHIPPEDLOTID = SHIPPEDLOT_ID FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND STATUS = 'TRANSIT' AND SHIP_STATUS = 0 AND SHIP_DATE = @SHIP_DATE AND SHIP_QTY = @SHIP_QTY
				--IF @SHIPPEDLOTID IS NULL 
	 			IF ( @@ROWCOUNT = 0 )
	 			BEGIN
					IF @TRANSACTION_TYPE = 'FG_Out'
					BEGIN
						INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NextLocation, SHIP_DROP_OUT, SL_NEXT_PROCESS) 
							VALUES( @LOT_ID, @SHIP_DATE, @SHIP_QTY, CURRENT_TIMESTAMP, 'Transit', 0,@NEXT_VENDOR_NAME, 0,@NEXT_PROCESS )
					END
					ELSE
					BEGIN
						INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NextLocation, SHIP_DROP_OUT, SL_NEXT_PROCESS) 
							VALUES( @LOT_ID, @SHIP_DATE, @SHIP_QTY, CURRENT_TIMESTAMP, 'Close', 1,@NEXT_VENDOR_NAME,  0, @NEXT_PROCESS )
					END
					SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
				END
				ELSE
				BEGIN
					UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
				END 
			END
			ELSE
			BEGIN
				-- DUMMY UPDATE TO AVOID SQLEXCEPTION "STATEMENTS THAT PRODUCE RESULT SETS ARE NOT ALLOWED IN BATCH COMMANDS."
				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
			END
		END--END OF ELSE BFGOUT AND BGOUT
	END--END OF LOT_ID NOT NULL

	DECLARE @MERGE_LOCATION_ID INT
	DECLARE @MERGE_PROCESS_STEP_ID INT
	IF @BATRCH_LOT IS NOT NULL AND @VENDOR_NAME = 'UTAC'
	BEGIN
		SELECT @MERGE_LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'TEST' AND L_VENDOR_NAME = @VENDOR_NAME
		SELECT @MERGE_PROCESS_STEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@PART_NUMBER,'TEST','FT' )

		SELECT @BATCHLOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @MERGE_PROCESS_STEP_ID AND LOCATION_ID = @MERGE_LOCATION_ID AND LOT_NUMBER = @BATRCH_LOT
		IF @BATCHLOT_ID IS NOT NULL
		BEGIN
			IF (Select COUNT(*) FROM Lot_Merge_Table WHERE Parent_Lot_Id = @BATCHLOT_ID AND Child_Lot_Id1 = @LOT_ID) = 0
			BEGIN
				INSERT INTO Lot_Merge_Table (Parent_Lot_Id,Child_Lot_Id1,Child_Lot_Qty1,User_Name,Timestamp )
					VALUES ( @BATCHLOT_ID, @LOT_ID, @SHIP_QTY, 'Adaptor', CURRENT_TIMESTAMP )
			END
		END			
	END --END OF UTAC BATCHING LOT

	IF @BATRCH_LOT IS NOT NULL AND @VENDOR_NAME = 'CARSEM'
	BEGIN
		SELECT @MERGE_LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
		SELECT @MERGE_PROCESS_STEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@PART_NUMBER,@PS_TYPE_NAME,@PS_TYPE )

		SELECT @BATCHLOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @MERGE_PROCESS_STEP_ID AND LOCATION_ID = @MERGE_LOCATION_ID AND LOT_NUMBER = @BATRCH_LOT
		IF @BATCHLOT_ID IS NOT NULL
		BEGIN
			IF (Select COUNT(*) FROM Lot_Merge_Table WHERE Parent_Lot_Id = @BATCHLOT_ID AND Child_Lot_Id1 = @LOT_ID) = 0
			BEGIN
				INSERT INTO Lot_Merge_Table (Parent_Lot_Id,Child_Lot_Id1,Child_Lot_Qty1,User_Name,Timestamp )
					VALUES ( @BATCHLOT_ID, @LOT_ID, @SHIP_QTY, 'Adaptor', CURRENT_TIMESTAMP )
			END
		END			
	END --END OF CARSEM BATCHING LOT
END
